import pandas as pd
from service.models import PredictionFact, Month
from service.utils import split_weeks, split_qtty_to_weeks
from users.models import Specialization, SpecializationType


def get_data_from_xlsx():
    PredictionFact.objects.all().delete()
    df = pd.read_excel("data.xlsx")
    df = df.fillna(0)
    df = df.astype(int)
    main_df = df.copy(deep=True).rename(columns={'Год':'year','Номер недели':'week'})
    df = df[['Год', 'Номер недели']].rename(columns={'Год':'year','Номер недели':'week'})
    expanded_df = split_weeks(df)
    main_df = main_df.merge(expanded_df,on=['year', 'week'])
    fields = [col for col in main_df.columns if col not in ['year', 'week', 'start_date', 'month', 'days_in_month']]
    for service in fields:
        main_df = split_qtty_to_weeks(main_df,service)
    fields = [f.split(' ') for f in fields]
    names = []
    for f in fields:
        spec,created = Specialization.objects.get_or_create(specialization=f[0])
        spec_type = ' '.join(f[1:]) if len(f)>1 else ''
        names.append(SpecializationType.objects.get_or_create(specialization=spec,type=spec_type)[0])
    # Применяем функцию распределения к каждой строке
    # names = [Specialization.objects.get_or_create(specialization=name)[0] for name in fields]
    for row, data in main_df.iterrows():
        for name in names:
            month,created = Month.objects.get_or_create(year=int(data['year']),month=int(data['month']))
            if data[name.specialization.specialization]!=0 and not PredictionFact.objects.filter(week=int(data['week']), month=month, specialization=name):
                obj, created = PredictionFact.objects.get_or_create(week=int(data['week']), specialization=name,qtty=int(data[name.specialization.specialization]), month=month)